package oracle

import (
	"context"
	"database/sql"
	"strconv"
	"strings"
	"testing"

	"github.com/google/go-cmp/cmp"
	"github.com/stretchr/testify/require"
	"google.golang.org/protobuf/testing/protocmp"

	"github.com/bytebase/bytebase/backend/common/testcontainer"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/plugin/schema"
)

// TestGetDatabaseDefinitionWithTestcontainer tests the GetDatabaseDefinition function
// by creating a schema, getting its definition, recreating it in a new database,
// and comparing the results.
func TestGetDatabaseDefinitionWithTestcontainer(t *testing.T) {
	if testing.Short() {
		t.Skip("Skipping Oracle testcontainer test in short mode")
	}

	ctx := context.Background()

	// Get Oracle container from testcontainer utility
	container := testcontainer.GetTestOracleContainer(ctx, t)
	defer container.Close(ctx)

	// Test cases with various schema configurations
	testCases := []struct {
		name          string
		initialSchema string
		description   string
	}{
		{
			name: "basic_tables_and_constraints",
			initialSchema: `
CREATE TABLE DEPARTMENTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    BUDGET NUMBER(12, 2) DEFAULT 0
);

CREATE TABLE EMPLOYEES (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    NAME VARCHAR2(100) NOT NULL,
    EMAIL VARCHAR2(100) UNIQUE,
    DEPARTMENT_ID NUMBER,
    SALARY NUMBER(10, 2),
    HIRE_DATE DATE DEFAULT SYSDATE,
    IS_ACTIVE NUMBER(1) DEFAULT 1 CHECK (IS_ACTIVE IN (0, 1)),
    CONSTRAINT FK_DEPT FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(ID)
);

CREATE INDEX IDX_EMP_DEPT ON EMPLOYEES(DEPARTMENT_ID);
CREATE INDEX IDX_EMP_NAME ON EMPLOYEES(NAME);
`,
			description: "Basic tables with primary keys, foreign keys, unique constraints, check constraints, and indexes",
		},
	}

	for _, tc := range testCases {
		t.Run(tc.name, func(t *testing.T) {
			// Get the database connection from the container
			testDB := container.GetDB()

			// Set current schema to TESTUSER
			_, err := testDB.Exec("ALTER SESSION SET CURRENT_SCHEMA = TESTUSER")
			require.NoError(t, err)

			// Clean up any existing objects
			cleanupSchema(t, testDB)

			// Step 1: Initialize the database schema and use SyncDBSchema to get metadata A
			err = executeStatements(testDB, tc.initialSchema)
			require.NoError(t, err, "Failed to execute initial schema")

			portInt, err := strconv.Atoi(container.GetPort())
			require.NoError(t, err)
			metadataA, err := getSyncMetadataForGenerateMigration(ctx, container.GetHost(), portInt)
			require.NoError(t, err, "Failed to get initial metadata")

			// Step 2: Call GetDatabaseDefinition to generate the database definition X
			definition, err := GetDatabaseDefinition(schema.GetDefinitionContext{}, metadataA)
			require.NoError(t, err, "Failed to generate database definition")
			require.NotEmpty(t, definition, "Generated definition should not be empty")

			// Log the generated definition for debugging
			t.Logf("Generated definition:\n%s", definition)

			// Step 3: Create a new database schema and run the database definition X
			// First, clean up the schema
			cleanupSchema(t, testDB)

			// Execute the generated definition
			err = executeStatements(testDB, definition)
			require.NoError(t, err, "Failed to execute generated definition")

			// Get metadata B after recreating from definition
			metadataB, err := getSyncMetadataForGenerateMigration(ctx, container.GetHost(), portInt)
			require.NoError(t, err, "Failed to get metadata after recreation")

			// Step 4: Compare the database metadata A and B, should be the same
			normalizeMetadataForComparison(metadataA)
			normalizeMetadataForComparison(metadataB)

			// Normalize column positions to 0 before comparison
			normalizeColumnPositions(metadataA)
			normalizeColumnPositions(metadataB)

			// Additional normalization for Oracle-specific issues
			normalizeOracleMetadata(metadataA)
			normalizeOracleMetadata(metadataB)

			// Use cmp with protocmp for proto message comparison
			if diff := cmp.Diff(metadataA, metadataB, protocmp.Transform()); diff != "" {
				t.Errorf("Schema mismatch after recreation (-original +recreated):\n%s", diff)
			}
		})
	}
}

// cleanupSchema drops all user objects in the schema
func cleanupSchema(t *testing.T, db *sql.DB) {
	// Set current schema to TESTUSER before cleanup
	_, err := db.Exec("ALTER SESSION SET CURRENT_SCHEMA = TESTUSER")
	require.NoError(t, err)

	// Drop all user objects in dependency order
	dropStatements := []string{
		// Drop triggers first
		"BEGIN FOR c IN (SELECT trigger_name FROM user_triggers) LOOP EXECUTE IMMEDIATE 'DROP TRIGGER ' || c.trigger_name; END LOOP; END;",
		// Drop materialized views
		"BEGIN FOR c IN (SELECT mview_name FROM user_mviews) LOOP EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || c.mview_name; END LOOP; END;",
		// Drop views
		"BEGIN FOR c IN (SELECT view_name FROM user_views) LOOP EXECUTE IMMEDIATE 'DROP VIEW ' || c.view_name || ' CASCADE CONSTRAINTS'; END LOOP; END;",
		// Drop synonyms
		"BEGIN FOR c IN (SELECT synonym_name FROM user_synonyms) LOOP EXECUTE IMMEDIATE 'DROP SYNONYM ' || c.synonym_name; END LOOP; END;",
		// Drop procedures
		"BEGIN FOR c IN (SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE') LOOP EXECUTE IMMEDIATE 'DROP PROCEDURE ' || c.object_name; END LOOP; END;",
		// Drop functions
		"BEGIN FOR c IN (SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION') LOOP EXECUTE IMMEDIATE 'DROP FUNCTION ' || c.object_name; END LOOP; END;",
		// Drop tables with cascade constraints
		"BEGIN FOR c IN (SELECT table_name FROM user_tables) LOOP EXECUTE IMMEDIATE 'DROP TABLE ' || c.table_name || ' CASCADE CONSTRAINTS'; END LOOP; END;",
		// Drop non-system sequences (system sequences cannot be dropped)
		"BEGIN FOR c IN (SELECT sequence_name FROM user_sequences WHERE sequence_name NOT LIKE 'ISEQ$$_%') LOOP EXECUTE IMMEDIATE 'DROP SEQUENCE ' || c.sequence_name; END LOOP; END;",
	}

	for _, stmt := range dropStatements {
		_, err := db.Exec(stmt)
		if err != nil {
			t.Logf("Warning during cleanup: %v", err)
		}
	}
}

// normalizeOracleMetadata handles Oracle-specific normalization for metadata comparison
func normalizeOracleMetadata(metadata *storepb.DatabaseSchemaMetadata) {
	for _, schema := range metadata.Schemas {
		for _, table := range schema.Tables {
			for _, column := range table.Columns {
				// Normalize system-generated sequence references in default expressions
				if column.Default != "" {
					// If it's a system-generated sequence, remove the default expression
					// since these sequences can't be manually recreated with the same name
					if strings.Contains(column.Default, "ISEQ$$_") {
						column.Default = ""
					}
				}

				// Clear collation information as we skip it in DDL generation
				column.Collation = ""

				// Normalize NVARCHAR2 type size differences
				// Oracle stores NVARCHAR2 sizes in max bytes, but DDL defines in characters
				if strings.HasPrefix(column.Type, "NVARCHAR2") {
					// Extract the size and convert to character count
					if strings.Contains(column.Type, "(") && strings.Contains(column.Type, ")") {
						// For simplicity, we'll normalize all NVARCHAR2 types to just NVARCHAR2
						// This avoids issues with byte vs character counting
						column.Type = "NVARCHAR2"
					}
				}
			}
		}
	}
}
